| 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@410
|
7 CREATE TABLE "issue_order_in_admission_state" (
|
|
jbe@400
|
8 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@410
|
9 "order_in_area" INT4,
|
|
jbe@410
|
10 "order_in_unit" INT4 );
|
|
jbe@396
|
11
|
|
jbe@410
|
12 COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';
|
|
jbe@398
|
13
|
|
jbe@410
|
14 COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
|
|
jbe@410
|
15 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';
|
|
jbe@410
|
16 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';
|
|
jbe@396
|
17
|
|
jbe@396
|
18 CREATE VIEW "issue_supporter_in_admission_state" AS
|
|
jbe@396
|
19 SELECT DISTINCT
|
|
jbe@410
|
20 "area"."unit_id",
|
|
jbe@396
|
21 "issue"."area_id",
|
|
jbe@396
|
22 "issue"."id" AS "issue_id",
|
|
jbe@396
|
23 "supporter"."member_id",
|
|
jbe@396
|
24 "direct_interest_snapshot"."weight"
|
|
jbe@396
|
25 FROM "issue"
|
|
jbe@410
|
26 JOIN "area" ON "area"."id" = "issue"."area_id"
|
|
jbe@396
|
27 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
|
|
jbe@396
|
28 JOIN "direct_interest_snapshot"
|
|
jbe@396
|
29 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
|
|
jbe@396
|
30 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
|
|
jbe@396
|
31 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
|
|
jbe@396
|
32 WHERE "issue"."state" = 'admission'::"issue_state";
|
|
jbe@396
|
33
|
|
jbe@396
|
34 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
|
35
|
|
jbe@396
|
36 COMMIT;
|